package com.scalar.chiptrack.adaptor.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.TestLot;
import com.scalar.chiptrack.adaptor.TestLotBinReport;


public final class TestAdaptorDAO implements AdaptorConstants
{
    private static Logger s_logger = null;

    public static void saveTestLot( DBTransaction dbTransaction, Iterator testLotIterator, String psType, String vendorName ) throws ScalarException
    {
        saveTestLot( dbTransaction, testLotIterator, psType, vendorName, true );
    }

    public static void saveTestLot( DBTransaction dbTransaction, Iterator testLotIterator, String psType, String vendorName, boolean closeLots ) throws ScalarException
    {

        Connection conn = null;
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }                                                                                           
            else
            {
                conn = dbTransaction.getConnection();
            }
            if ( vendorName != null )
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "adaptor.closeAllWipInventoryLots" ) );
                pstmt.setString( 1, psType );
                pstmt.setString( 2, vendorName );
                pstmt.executeUpdate();

                pstmt.close();
                pstmt = null;

            }
            else
            {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCS);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCK);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCC);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCM);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }

            success = true;


            // Step 2
            // Run new values
            cstmt = conn.prepareCall( SqlQueryMappings.get( "adaptor.saveTestLot" ) );

            TestLot testLot = null;

            log( "Saving Test Lot Information... " );
            while( testLotIterator.hasNext() )
            {
                testLot = ( TestLot ) testLotIterator.next();

                if (testLot.getLotNumber() == null) continue;
                log( testLot + "\n" );

                cstmt.setString( 1, testLot.getPartNumber() );
                cstmt.setString( 2, testLot.getLotNumber() );
                cstmt.setString( 3, testLot.getVendorLot() );
                cstmt.setString( 4, testLot.getLeadCount() );
                cstmt.setString( 5, testLot.getPackageType() );
                cstmt.setString( 6, testLot.getStage() );
                cstmt.setString( 7, testLot.getDateIn() );
                cstmt.setString( 8, testLot.getDateOut() );
                cstmt.setString( 9, testLot.getQtyIn() );
                cstmt.setString( 10, testLot.getQtyOut() );
                cstmt.setString( 11, testLot.getPoNumber() );
                cstmt.setString( 12, testLot.getStatus() );
                cstmt.setString( 13, testLot.getExpecetedDate() );
                cstmt.setString( 14, testLot.getVendorName() );
                cstmt.setString( 15, testLot.getPsType()  );
                cstmt.setString( 16, testLot.getHoldCode() );
                cstmt.setString( 17, testLot.getHoldReason() );
                cstmt.setString( 18, testLot.getFabLotNumber() );
                cstmt.setString( 19, testLot.getCategory() );
                cstmt.setString( 20, testLot.getDateCode() );
                cstmt.setString( 21, testLot.getTestProgramRevision() );

                cstmt.addBatch();
            }

            cstmt.executeBatch();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Test Lot Information into database", sqle );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                    ignore.printStackTrace();
                }
            }

            if( cstmt != null )
            {
                try
                {
                    cstmt.close();
                }
                catch( SQLException ignore )
                {
                    ignore.printStackTrace();
                }
            }

            if( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch( SQLException ignore )
                {
                }
            }

            if( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch( SQLException ignore )
                {
                }
            }
        }
    }

    /**
     * This method is mainly closes the running lots explicitly.Reason for writing this
     * method suppose today if I get the empty list from the Adaptor, those which are running
     * state lots are not closing. we need to explicitly close those type of lots.
     *
     * @param dbTransaction
     * @param psType
     * @param vendorAdaptorName
     * @throws ScalarException
     */

    //Added by ChandraSekhar 0n 07th August 2004.

    public static void closeTestLot( DBTransaction dbTransaction, String psType, String vendorAdaptorName ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;

        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if ( vendorAdaptorName != null )
            {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, vendorAdaptorName);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }
            else
            {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCS);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCK);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCC);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCM);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }

            success = true;

        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Test Lot Information into database", sqle );
        }

        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                    ignore.printStackTrace();
                }

            }

            if( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch( SQLException ignore )
                {
                    ignore.printStackTrace();
                }
            }

            if( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch( SQLException ignore )
                {
                    ignore.printStackTrace();
                }
            }
        }
    }

    public static void saveTestBinInfo ( DBTransaction dbTransaction, ArrayList ftBinInfoList, String vendorName ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;

        TestLotBinReport ftBinInfo = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall( "{ CALL SP_CREATE_UPDATE_TEST_BIN_INFO( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,? ) }" );
            int listSize = (ftBinInfoList == null ? 0 : ftBinInfoList.size());
            for ( int i=0; i < listSize; i++ )
            {
                ftBinInfo = (TestLotBinReport) ftBinInfoList.get(i);
                log( ftBinInfo + "" );
                cstmt.setString( 1, ftBinInfo.getDeviceName() );
                cstmt.setString( 2, ftBinInfo.getLotNumber() );
                cstmt.setString( 3, ftBinInfo.getCustLotNo() );
                cstmt.setString( 4, ftBinInfo.getTestProg() );
                cstmt.setString( 5, ftBinInfo.getDateIn() );
                cstmt.setString( 6, ftBinInfo.getDateOut() );
                cstmt.setString( 7, vendorName );
                cstmt.setString( 8, ftBinInfo.getTesterId() );
                cstmt.setString( 9, ftBinInfo.getDateCode() );
                cstmt.setString( 10, ftBinInfo.getPkgType() );
                cstmt.setString( 11, ftBinInfo.getInQty() );
                cstmt.setString( 12, ftBinInfo.getPassQty() );
                cstmt.setString( 13, ftBinInfo.getBin1() );
                cstmt.setString( 14, ftBinInfo.getBin2() );
                cstmt.setString( 15, ftBinInfo.getBin3() );
                cstmt.setString( 16, ftBinInfo.getBin4() );
                cstmt.setString( 17, ftBinInfo.getBin5() );
                cstmt.setString( 18, ftBinInfo.getBin6() );
                cstmt.setString( 19, ftBinInfo.getBin7() );
                cstmt.setString( 20, ftBinInfo.getBin8() );
                cstmt.setString( 21, ftBinInfo.getBin9() );
                cstmt.setString( 22, ftBinInfo.getBin10() );
                cstmt.setString( 23, ftBinInfo.getBin11() );
                cstmt.setString( 24, ftBinInfo.getBin12() );
                cstmt.setString( 25, ftBinInfo.getBin13() );
                cstmt.setString( 26, ftBinInfo.getBin14() );
                cstmt.setString( 27, ftBinInfo.getBin15() );
                cstmt.setString( 28, ftBinInfo.getHandler() );
                cstmt.setString( 29, ftBinInfo.getOsQty() );
                cstmt.setString( 30, ftBinInfo.getOSRate() );
                cstmt.setString( 31, ftBinInfo.getDamageQty() );
                cstmt.setString( 32, ftBinInfo.getLossQty() );
                cstmt.setString( 33, ftBinInfo.getScanInQty() );
                cstmt.setString( 34, ftBinInfo.getScanOutQty() );
                cstmt.setString( 35, ftBinInfo.getPoNumber() );
                cstmt.setString( 36, ftBinInfo.getLeadCount() );
                cstmt.setString( 37, ftBinInfo.getLb() );
                cstmt.setString( 38, ftBinInfo.getTemp() );
                cstmt.setString( 39, ftBinInfo.getStage() );
                cstmt.setString( 40, ftBinInfo.getVmQty() );
                cstmt.setString( 41, ftBinInfo.getYield() );
                cstmt.setString( 42, ftBinInfo.getDutBoardNo() );
                cstmt.setString( 43, ftBinInfo.getDutCableNo() );
                cstmt.addBatch();

            }
            cstmt.executeBatch();

            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not save Test BINNING INFO", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && success )
            {
                try { conn.commit(); } catch ( SQLException ignore ) {}
            }
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore ) {}
            }
            if ( conn != null )
            {
                 try { conn.close(); } catch( SQLException ignore ) {}
            }
        }
    }

    public static void saveTestHardSoftBinInfo ( DBTransaction dbTransaction, ArrayList ftBinInfoList, String vendorName ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;

        TestLotBinReport ftBinInfo = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall( "{ CALL SP_CREATE_UPDATE_TEST_SOFT_HARD_BIN_INFO( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?" +
                    " ,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,? ) }" );

            int listSize = (ftBinInfoList == null ? 0 : ftBinInfoList.size());
            for ( int i=0; i < listSize; i++ )
            {
                ftBinInfo = (TestLotBinReport) ftBinInfoList.get(i);
                log( ftBinInfo + "" );
                cstmt.setString( 1, ftBinInfo.getDeviceName() );
                cstmt.setString( 2, ftBinInfo.getLotNumber() );
                cstmt.setString( 3, ftBinInfo.getCustLotNo() );
                cstmt.setString( 4, ftBinInfo.getTestProg() );
                cstmt.setString( 5, ftBinInfo.getDateIn() );
                cstmt.setString( 6, ftBinInfo.getDateOut() );
                cstmt.setString( 7, vendorName );
                cstmt.setString( 8, ftBinInfo.getTesterId() );
                cstmt.setString( 9, ftBinInfo.getDateCode() );
                cstmt.setString( 10, ftBinInfo.getPkgType() );
                cstmt.setString( 11, ftBinInfo.getInQty() );
                cstmt.setString( 12, ftBinInfo.getPassQty() );
                cstmt.setString( 13, ftBinInfo.getBin1() );
                cstmt.setString( 14, ftBinInfo.getBin2() );
                cstmt.setString( 15, ftBinInfo.getBin3() );
                cstmt.setString( 16, ftBinInfo.getBin4() );
                cstmt.setString( 17, ftBinInfo.getBin5() );
                cstmt.setString( 18, ftBinInfo.getBin6() );
                cstmt.setString( 19, ftBinInfo.getBin7() );
                cstmt.setString( 20, ftBinInfo.getBin8() );
                cstmt.setString( 21, ftBinInfo.getBin9() );
                cstmt.setString( 22, ftBinInfo.getBin10() );
                cstmt.setString( 23, ftBinInfo.getBin11() );
                cstmt.setString( 24, ftBinInfo.getBin12() );
                cstmt.setString( 25, ftBinInfo.getBin13() );
                cstmt.setString( 26, ftBinInfo.getBin14() );
                cstmt.setString( 27, ftBinInfo.getBin15() );
                cstmt.setString( 28, ftBinInfo.getHandler() );
                cstmt.setString( 29, ftBinInfo.getOsQty() );
                cstmt.setString( 30, ftBinInfo.getOSRate() );
                cstmt.setString( 31, ftBinInfo.getDamageQty() );
                cstmt.setString( 32, ftBinInfo.getLossQty() );
                cstmt.setString( 33, ftBinInfo.getScanInQty() );
                cstmt.setString( 34, ftBinInfo.getScanOutQty() );
                cstmt.setString( 35, ftBinInfo.getPoNumber() );
                cstmt.setString( 36, ftBinInfo.getLeadCount() );
                cstmt.setString( 37, ftBinInfo.getLb() );
                cstmt.setString( 38, ftBinInfo.getTemp() );
                cstmt.setString( 39, ftBinInfo.getStage() );
                cstmt.setString( 40, ftBinInfo.getVmQty() );
                cstmt.setString( 41, ftBinInfo.getYield() );
                cstmt.setString( 42, ftBinInfo.getDutBoardNo() );
                cstmt.setString( 43, ftBinInfo.getDutCableNo() );
                int[] binQty = ftBinInfo.getSoftbinQty();
                for ( int k=0; k < 40; k++ )
                {
                    cstmt.setInt( (k+44), binQty[k] );
                }
                cstmt.addBatch();

            }
            cstmt.executeBatch();

            success = true;
        }
        catch ( Exception e )                  
        {
            throw new ScalarException ( "Could not save Test BINNING INFO", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && success )
            {
                try { conn.commit(); } catch ( SQLException ignore ) {}
            }
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore ) {}
            }
            if ( conn != null )
            {
                 try { conn.close(); } catch( SQLException ignore ) {}
            }
        }
    }

    private static void log( String message )
    {

        if( message == null ) return;

        if( s_logger != null )
        {
            synchronized( s_logger )
            {
                s_logger.logDebug( message + LINE_SEPARATOR );
            }
        }
        else
        {
            System.out.println( message );
        }
    }

    public static void setLogger( Logger logger )
    {
        s_logger = logger;
    }

}